﻿using System;
using System.Collections;
using System.Collections.Specialized;
using System.Data;
using System.Data.SQLite;
using System.Configuration;
namespace JCode.DBUtility
{
   public  class SQLite
    {

       private static readonly string StrConn = ConfigurationManager.AppSettings["sqliteconn"];

       public static DataSet GetDataSet(string sql)
       {
           using (SQLiteConnection conn = new SQLiteConnection(StrConn))
           {
               DataSet ds = new DataSet();
               try
               {
                   if (conn.State == ConnectionState.Closed)
                       conn.Open();
                   SQLiteDataAdapter dap = new SQLiteDataAdapter(sql, conn);
                   dap.Fill(ds, "ds");
               }
               catch
               {
               }
               finally
               {
                   conn.Close();
               }
               return ds;

           }
       }

       /// <summary>
       /// 执行SQL语句，返回影响的记录数
       /// </summary>
       /// <param name="SQLString">SQL语句</param>
       /// <returns>影响的记录数</returns>
       public static int ExecuteSql(string SQLString)
       {
           using (SQLiteConnection conn = new SQLiteConnection(StrConn))
           {
               using (SQLiteCommand cmd = new SQLiteCommand(SQLString, conn))
               {
                   try
                   {
                       if (conn.State == ConnectionState.Closed)
                           conn.Open();
                       int rows = cmd.ExecuteNonQuery();
                       return rows;
                   }
                   catch (System.Data.SQLite.SQLiteException E)
                   {
                      throw new Exception(E.Message);
                   }
                   finally
                   {
                       cmd.Dispose();
                       conn.Close();

                   }

               }
           }
       }


       #region 执行参数化SQL
       /// <summary>
       /// 执行SQL语句，返回影响的记录数
       /// </summary>
       /// <param name="SQLString">SQL语句</param>
       /// <returns>影响的记录数</returns>
       public static int ExecuteSql(string SQLString, params SQLiteParameter[] cmdParms)
       {
           using (SQLiteConnection conn = new SQLiteConnection(StrConn))
           {
               using (SQLiteCommand cmd = new SQLiteCommand())
               {
                   try
                   {
                       PrepareCommand(cmd, conn, null, SQLString, cmdParms);
                       int rows = cmd.ExecuteNonQuery();
                       cmd.Parameters.Clear();
                       return rows;
                   }
                   catch (System.Data.SQLite.SQLiteException E)
                   {
                       throw new Exception(E.Message);
                   }
               }
           }
       }

       #endregion

       #region 参数转换
       /// <summary>
       /// 放回一个SQLiteParameter
       /// </summary>
       /// <param name="name">参数名字</param>
       /// <param name="type">参数类型</param>
       /// <param name="size">参数大小</param>
       /// <param name="value">参数值</param>
       /// <returns>SQLiteParameter的值</returns>
       public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, int size, object value)
       {
           SQLiteParameter parm = new SQLiteParameter(name, type, size);
           parm.Value = value;
           return parm;
       }

       public static SQLiteParameter MakeSQLiteParameter(string name, DbType type, object value)
       {
           SQLiteParameter parm = new SQLiteParameter(name, type);
           parm.Value = value;
           return parm;
       }

       #endregion

       private static void PrepareCommand(SQLiteCommand cmd, SQLiteConnection conn, string cmdText, params object[] p)
        {



            if (conn.State != ConnectionState.Open)

                conn.Open();

            cmd.Parameters.Clear();

            cmd.Connection = conn;

            cmd.CommandText = cmdText;



            cmd.CommandType = CommandType.Text;

            cmd.CommandTimeout = 30;



            if (p != null)
            {

                foreach (object parm in p)

                    cmd.Parameters.AddWithValue(string.Empty, parm);
               

            }

        }


    }

}




